﻿using BCCommon;
using BCCommon.TMS.Transport;
using BCEntity.TMS.VehicleTransportBusinessConfig;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace BCData.TMS.VehicleTransportBusinessConfig
{
    public class VehicleTransportBusinessConfigOverwriteData : IVehicleTransportBusinessConfigOverwriteData
    {
        private readonly IDatabaseContext database;

        public VehicleTransportBusinessConfigOverwriteData(IDatabaseContext database)
        {
            this.database = database;
        }

		private const string QUERY = @"SELECT
										VehicleTransportBusinessConfigOverwriteId,
										VehicleTransportBusinessConfigId,
										TransportBusiness,
										Enabled,
										BusinessOrder,
										Remark,
                                        SameCityChargingMode,
                                        SameCityPointSurchargeAmount,
										ProvinceCode,
										CityCode,
										CountyCode,
										AgentRecordId,
										CreateAdminId,
										CreateAdminName,
										CreateTime,
										UpdateTime,
										EditAdminId,
										EditAdminName 
									FROM
										VehicleTransportBusinessConfigOverwrite";

        public async Task<(IEnumerable<VehicleTransportBusinessConfigViewEntity>, int)> Query(long? agentRecordId, TransportBusinessType? transportBusiness, string provinceCode, string cityCode, string countyCode, int pageIndex, int pageSize)
        {
			List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
			if (agentRecordId.HasValue)
			{
				wheres.Add("A.AgentRecordId = ?AgentRecordId");
				parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
			}
			if (transportBusiness.HasValue)
            {
				wheres.Add("A.TransportBusiness = ?TransportBusiness");
				parameters.Add("?TransportBusiness", transportBusiness.Value.ToString(), DbType.AnsiString);
            }
			if (!string.IsNullOrWhiteSpace(provinceCode))
            {
				wheres.Add("A.ProvinceCode = ?ProvinceCode");
				parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
			if (!string.IsNullOrWhiteSpace(cityCode))
            {
				wheres.Add("A.CityCode = ?CityCode");
				parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
			if (!string.IsNullOrWhiteSpace(countyCode))
            {
				wheres.Add("A.CountyCode = ?CountyCode");
				parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
			//string sql = @"SELECT C.AgentRecordId,C.ProvinceCode,D.ProvinceName,C.CityCode,D.CityName,C.CountyCode,D.CountyName,C.IsChildAgent,C.IsEpibole,
			//				C.VehicleTransportBusinessConfigId,C.TransportBusiness,E.VehicleTransportBusinessConfigOverwriteId,
			//				CASE WHEN E.Enabled IS NULL THEN C.Enabled ELSE E.Enabled END AS Enabled,
			//				CASE WHEN E.BusinessOrder IS NULL THEN C.BusinessOrder ELSE E.BusinessOrder END AS BusinessOrder,
			//				C.Remark 
			//			FROM
			//			(
			//				SELECT A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.IsChildAgent,A.IsEpibole,B.VehicleTransportBusinessConfigId,B.TransportBusiness,B.Enabled,B.BusinessOrder,B.Remark  
			//				FROM AgentAreaRecord A,VehicleTransportBusinessConfig B
			//				WHERE A.AgentRecordId = ?AgentRecordId
			//			) C
			//			INNER JOIN County D ON D.ProvinceCode = C.ProvinceCode AND D.CityCode = C.CityCode AND D.CountyCode = C.CountyCode
			//			LEFT JOIN VehicleTransportBusinessConfigOverwrite E 
			//			ON E.AgentRecordId = ?AgentRecordId 
			//			AND C.VehicleTransportBusinessConfigId = E.VehicleTransportBusinessConfigId 
			//			AND C.TransportBusiness = E.TransportBusiness
			//			AND C.ProvinceCode = E.ProvinceCode
			//			AND C.CityCode = E.CityCode
			//			AND C.CountyCode = E.CountyCode";
			//string countSql = @"SELECT COUNT(1) FROM
			//					(
			//						SELECT A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.IsChildAgent,A.IsEpibole,B.VehicleTransportBusinessConfigId,B.TransportBusiness,B.Enabled  
			//						FROM AgentAreaRecord A,VehicleTransportBusinessConfig B
			//						WHERE A.AgentRecordId = ?AgentRecordId
			//					) C
			//					INNER JOIN County D ON D.ProvinceCode = C.ProvinceCode AND D.CityCode = C.CityCode AND D.CountyCode = C.CountyCode
			//					LEFT JOIN VehicleTransportBusinessConfigOverwrite E 
			//					ON E.AgentRecordId = ?AgentRecordId 
			//					AND C.VehicleTransportBusinessConfigId = E.VehicleTransportBusinessConfigId 
			//					AND C.TransportBusiness = E.TransportBusiness
			//					AND C.ProvinceCode = E.ProvinceCode
			//					AND C.CityCode = E.CityCode
			//					AND C.CountyCode = E.CountyCode";

			string sql = @"SELECT A.AgentRecordId,A.ProvinceCode,B.ProvinceName,A.CityCode,B.CityName,A.CountyCode,B.CountyName,
							A.VehicleTransportBusinessConfigId,A.TransportBusiness,A.VehicleTransportBusinessConfigOverwriteId,
							A.Enabled,A.BusinessOrder,A.Remark, A.CreateAdminId,A.CreateAdminName,
							A.CreateTime,A.UpdateTime,A.EditAdminId,A.EditAdminName 
						FROM VehicleTransportBusinessConfigOverwrite A 
						INNER JOIN County B ON A.ProvinceCode = B.ProvinceCode AND A.CityCode = B.CityCode AND A.CountyCode = B.CountyCode";
			string countSql = @"SELECT COUNT(1) FROM VehicleTransportBusinessConfigOverwrite A ";

			sql = string.Format(@"{0} {1} LIMIT {2},{3};{4} {1}",
								sql,
								wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
								(pageIndex - 1) * pageSize,
								pageSize,
								countSql);
			return await database.Slave.QueryPageableListAsync<VehicleTransportBusinessConfigViewEntity>(sql, parameters);
        }

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> GetEntity(int vehicleTransportBusinessConfigOverwriteId)
        {
			return await database.Slave.QueryEntityAsync<VehicleTransportBusinessConfigOverwriteEntity>($"{QUERY} WHERE VehicleTransportBusinessConfigOverwriteId = ?VehicleTransportBusinessConfigOverwriteId", new { vehicleTransportBusinessConfigOverwriteId });
        }

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> Update(VehicleTransportBusinessConfigOverwriteEntity entity)
        {
			return await database.Master.UpdateAsync(entity);
        }

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> GetEntity(long agentRecordId, int vehicleTransportBusinessConfigId, string provinceCode, string cityCode, string countyCode, string transportBusiness)
        {
			return await database.Slave.QueryEntityAsync<VehicleTransportBusinessConfigOverwriteEntity>($@"{QUERY} WHERE AgentRecordId = ?AgentRecordId 
												AND VehicleTransportBusinessConfigId = ?VehicleTransportBusinessConfigId AND ProvinceCode = ?ProvinceCode 
												AND CityCode = ?CityCode AND CountyCode = ?CountyCode AND TransportBusiness = ?TransportBusiness", 
												new { agentRecordId, vehicleTransportBusinessConfigId, provinceCode, cityCode, countyCode, transportBusiness });
        }

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> Insert(VehicleTransportBusinessConfigOverwriteEntity entity)
        {
			return await database.Master.InsertAsync(entity);
        }

        public async Task<int> DeleteByConfigOverwriteId(int vehicleTransportBusinessConfigOverwriteId)
        {
			return await database.Master.ExecuteAsync("DELETE FROM VehicleTransportBusinessConfigOverwrite WHERE VehicleTransportBusinessConfigOverwriteId = ?VehicleTransportBusinessConfigOverwriteId", new { vehicleTransportBusinessConfigOverwriteId });
        }

        public async Task<IEnumerable<VehicleTransportBusinessConfigViewEntity>> GetList(long agentRecordId,string countyCode)
        {
			string sql = string.Format("{0} WHERE AgentRecordId=?AgentRecordId AND CountyCode=?CountyCode", QUERY);
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
			parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
			return await this.database.Slave.QueryAsync<VehicleTransportBusinessConfigViewEntity>(sql, parameters);
		}

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> GetEntityByConfigId(int vehicleTransportBusinessConfigId)
        {
			return await database.Slave.QueryEntityAsync<VehicleTransportBusinessConfigOverwriteEntity>($"{QUERY} WHERE VehicleTransportBusinessConfigId = ?VehicleTransportBusinessConfigId LIMIT 1", new { vehicleTransportBusinessConfigId });
        }

        public async Task<VehicleTransportBusinessConfigOverwriteEntity> GetList(long? agentRecordId, string countyCode, TransportBusinessType? transportBusinessType)
        {
			List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
			if (agentRecordId.HasValue)
            {
				wheres.Add("AgentRecordId = ?AgentRecordId");
				parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
			if (!string.IsNullOrWhiteSpace(countyCode))
            {
				wheres.Add("CountyCode = ?CountyCode");
				parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
			if (transportBusinessType.HasValue)
            {
				wheres.Add("TransportBusiness = ?TransportBusiness");
				parameters.Add("?TransportBusiness", transportBusinessType.Value.ToString(), DbType.AnsiString);
            }
			wheres.Add("Enabled = ?Enabled");
			parameters.Add("?Enabled", true, DbType.Boolean);
			string sql = string.Format("{0} {1} LIMIT 1", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
			return await database.Slave.QueryEntityAsync<VehicleTransportBusinessConfigOverwriteEntity>(sql, parameters);
        }
    }
}
